﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Collections;
using System.Text;
using System.Threading.Tasks;
using GalleryCommon;

namespace GalleryDataProvider
{
    public static class PictureDataProvider
    {
        private static readonly SqlConnection myConnection = ConnectionProvider.Connection;

        public static bool AddNewPicture(string name, DateTime date, long albumID, string description, byte[] picturefile)
        {
            bool result = true;

            SqlCommand command;

            if (true)   //For later use
            {
                command = new SqlCommand("insert into Pictures values (@Name, @Date, @AlbumID, @Description, @PictureFile)", myConnection);
                command.Parameters.AddWithValue("Name", name);
                command.Parameters.AddWithValue("Date", date);
                command.Parameters.AddWithValue("AlbumID", albumID);
                command.Parameters.AddWithValue("Description", description);
                command.Parameters.AddWithValue("PictureFile", picturefile);

                try
                {
                    command.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    result = false;
                }
                return result;
            }

            //return false;
        }

        //AlbumID alapján
        public static bool GivePicture(long AlbumID, out List<Picture> pictures, int LastPicNo)
        {
            pictures = new List<Picture>();
            bool result = true;

            //SqlServer 2005+ compatible
            SqlCommand command = new SqlCommand("select top 9 * from ( " +
                                                    "select *, ROW_NUMBER() over(order by PictureID ASC) as r_n_n " +
                                                    "from Pictures " +
                                                    "where AlbumID=@AlbumID " +
                                                ") xx where r_n_n >=@LastPicNo", 
                                                myConnection);
            /* SqlServer 2012+ compatible
            SqlCommand command = new SqlCommand("SELECT PictureFile, PictureID " +
                                                "FROM Pictures WHERE AlbumID=@AlbumID " +
                                                "ORDER BY PictureID ASC " +
                                                "OFFSET  @LastPicNo ROWS " +
                                                "FETCH NEXT 9 ROWS ONLY", myConnection);
                                                */
            command.Parameters.AddWithValue("AlbumID", AlbumID);
            command.Parameters.AddWithValue("LastPicNo", LastPicNo);
    
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        pictures.Add(
                            new Picture
                            {
                                PictureFile = (byte[])reader.GetSqlBinary(0),
                                PictureID = reader.GetInt64(1)
                            });
                    }
                }
                reader.Close();
            }
            catch (SqlException)
            {
                reader.Close();
                result = false;
            }
            reader.Close();
            return result;
        }

        //PictureID alapján
        public static bool GivePictureByPicID(long PicID, out Picture picture)
        {
            bool result = true;

            SqlCommand command = new SqlCommand("SELECT PictureFile FROM Pictures WHERE PictureID=@PictureID", myConnection);
            command.Parameters.AddWithValue("PictureID", PicID);

            Picture pic = new Picture();
            SqlDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        pic.PictureFile = (byte[])reader.GetSqlBinary(0);
                        pic.PictureID = PicID;
                    }
                }
            reader.Close();
            }
            catch (SqlException)
            {
                reader.Close();
                result = false;
            }
            reader.Close();
            picture = pic;
            return result;
        }

        /// <summary>
        /// Beszúrja az adatbázisba a nyilvános képhez tartozó generált azonosítót
        /// </summary>
        /// <param name="PicID">a kép azonosítója</param>
        /// <param name="PublicID">a generált egyedi azonosító</param>
        /// <returns></returns>
        public static bool InsertPublicLink(long PicID, string PublicID)
        {
            try
            {
                SqlCommand command = new SqlCommand("INSERT INTO PublicPictures VALUES (@Link , @picID)", myConnection);
                command.Parameters.AddWithValue("pubID", PublicID);
                command.Parameters.AddWithValue("picID", PicID);

                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        /// <summary>
        /// Lekérdezi az adatbázisból a nyilvános linkkel rendelkező képet
        /// </summary>
        /// <param name="PublicID">nyilvános linkben szereplő nyilvános azonosító</param>
        /// <param name="Pic">ez lesz a lekérdezett kép</param>
        public static bool GivePictureByPublicID(string PublicID, out Picture Pic)
        {
            try
            {
                SqlCommand command = new SqlCommand("SELECT * FROM PublicPictures WHERE PublicID=@pubID", myConnection);
                command.Parameters.AddWithValue("pubID", PublicID);

                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    long picID = reader.GetInt64(1);
                    GivePictureByPicID(picID, out Pic);
                    return true;
                }

                Pic = null;
                return false;
            }
            catch (Exception)
            {
                Pic = null;
                return false;
            }
        }
    }
}